package com.correo.database.client;

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.LinkedList;
import java.util.List;

import com.correo.database.beans.CasillaCorreoBean;
import com.correo.database.beans.Configuracion;
import com.correo.database.beans.DataBaseBean;
import com.correo.database.beans.UserBean;

public class ManagerDataBaseClient {

	private static ManagerDataBaseClient 	managerDataBase;
	private Connection				connection;
	
	public static ManagerDataBaseClient getInstance() throws ClassNotFoundException, SQLException{
		if(managerDataBase==null){
			managerDataBase = new ManagerDataBaseClient();
		}
		return managerDataBase;
	}
	
	private ManagerDataBaseClient() throws ClassNotFoundException, SQLException{
		setConnection();
	}
	
	public Connection getConnection() throws SQLException, ClassNotFoundException{
		if(connection==null || connection.isClosed()){
			setConnection();
		}
		
		return connection;
	}

	private void setConnection() throws ClassNotFoundException, SQLException {		        	
		Class.forName(ConfiguracionClient.DRIVER);
        connection = DriverManager.getConnection(ConfiguracionClient.URL,ConfiguracionClient.USER,ConfiguracionClient.PASSWORD);
	}
	
	//ejecutando los sql
	
	public List<DataBaseBean> select(DataBaseBean dataBaseBean) throws SQLException, ClassNotFoundException{
		
		PreparedStatement preparedStatement = getConnection().prepareCall(dataBaseBean.getSelect());
		dataBaseBean.setPreparedSql(preparedStatement);
		
		return getResult(dataBaseBean,preparedStatement.executeQuery());
	}

	private List<DataBaseBean> getResult(DataBaseBean dataBaseBean, ResultSet result) throws SQLException {
		List<DataBaseBean> listResult=new LinkedList<DataBaseBean>();
		
		while(result.next()){
			listResult.add(dataBaseBean.getDataBean(result));
		}
		return listResult;
	}
	
	public List<DataBaseBean> insert(DataBaseBean dataBaseBean) throws SQLException, ClassNotFoundException{
		PreparedStatement preparedStatement = getConnection().prepareCall(dataBaseBean.getInsert());
		dataBaseBean.setPreparedSql(preparedStatement);
		
		return null;
	}

	public List<CasillaCorreoBean> CasillaCorreo(UserBean userConnect) throws SQLException, ClassNotFoundException {
		
		List<CasillaCorreoBean> listmails = new LinkedList<CasillaCorreoBean>();
		CallableStatement cstmt = getConnection().prepareCall("{CALL usp_casillaCorreoByUserId(?)}");
		cstmt.setString(1,userConnect.getId());
		ResultSet rst = cstmt.executeQuery(); 
		
		while (rst.next()){
			CasillaCorreoBean casilla = new CasillaCorreoBean();
			UserBean	user = new UserBean();
		
			casilla.setId(rst.getString("casillaCorreoId"));
			user.setId(rst.getString("userId"));
			user.setName(rst.getString("name"));
			user.setApellido(rst.getString("apellido"));
			
			casilla.setUser(user);
			
			listmails.add(casilla);
		}
		cstmt.close();
		return listmails;
	}

	public List<CasillaCorreoBean> getCasillaCorreoAgenda(CasillaCorreoBean casilla) throws SQLException, ClassNotFoundException {
		List<CasillaCorreoBean> listmails = new LinkedList<CasillaCorreoBean>();
		CallableStatement cstmt = getConnection().prepareCall("{CALL usp_userAgendaCasillaCorreo(?)}");
		cstmt.setString(1,casilla.getId());
		ResultSet rst = cstmt.executeQuery(); 
		
		while (rst.next()){
			CasillaCorreoBean casillaCorreoAgenda = new CasillaCorreoBean();
			UserBean	user = new UserBean();		

			casillaCorreoAgenda.setId(rst.getString("casillaCorreoId"));
			user.setId(rst.getString("userId"));
			user.setName(rst.getString("name"));
			user.setApellido(rst.getString("apellido"));
			
			casillaCorreoAgenda.setUser(user);
			
			listmails.add(casillaCorreoAgenda);
		}
		cstmt.close();
		return listmails;
	}
}
